﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace GD.VehicleManage.Data.Databese
{
    public class DbStore : IDbStore
    {
        /// <summary>
        /// 测试连接数据库是否成功
        /// </summary>
        /// <returns></returns>
        public bool ConnectionTest(string connectionString)
        {
            var isSuccess = false;
            var conn = new SqlConnection(connectionString);
            try
            {
                conn.Open();
                isSuccess = true;
            }
            catch
            {
                isSuccess = false;
            }
            finally
            {
                conn.Close();
            }

            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
                return isSuccess;
            else
                return isSuccess;
        }

        /// <summary>
        /// 创建数据库连接字符串
        /// https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
        /// </summary>
        /// <returns></returns>
        public string CreateConnectioning(string server, string uid, string pwd, bool integratedSecurity = false, string db = "master",
            int connectTimeout = 15, int connectionLifetime = 0, bool pooling = true, int maxPoolSize = 100, int minPoolSize = 0,
            bool multipleActiveResultSets = false)
        {
            var connString = integratedSecurity ? $"server={server};database={db};Integrated Security=True;" : $"server={server};uid={uid};pwd={pwd};database={db};";
            if (connectTimeout != 15)
                connString += $"connect timeout={connectTimeout.ToString()};";
            if (connectionLifetime != 0)
                connString += $"Connection Lifetime={connectionLifetime.ToString()};";
            if (!pooling)
                connString += $"Pooling=false;";
            if (minPoolSize != 0)
                connString += $"Min Pool Size={minPoolSize.ToString()};";
            if (maxPoolSize != 100)
                connString += $"MAX Pool Size={maxPoolSize.ToString()};";
            if (multipleActiveResultSets)
                connString += $"MultipleActiveResultSets=true;";

            return connString;
        }
        public void BackupsDatabase(string db, string connectionString, string path)
        {
            string sql = string.Format("backup database {0} to disk='{1}{0}.bak'", db, path);

            // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int count = connection.Execute(sql);
            }
        }

        public void BackupsDatabase(List<string> list, string connectionString, string path)
        {
            foreach (var item in list)
            {
                BackupsDatabase(item, connectionString, path);
            }
        }

        /// <summary>
        /// 获取数据库列表
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public List<string> GetAllUserDatabeseName(string connectionString)
        {
            string sql = "select [name] from master.dbo.sysdatabases where dbid > 6 Order By [Name]";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<string>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }

        public List<DatabaseDAO> GetAllUserDatabese(string connectionString)
        {
            //List<DBName> list =new List<DBName>();
            string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] ";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<DatabaseDAO>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }

        public List<string> GetAllTableNameByDb(string connectionString)
        {
            string sql = "SELECT TABLE_NAME as name FROM INFORMATION_SCHEMA.TABLES";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<string>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }

        public List<TableDAO> GetAllTableByDb(string connectionString)
        {
            string sql = $"SELECT TABLE_NAME as name,(SELECT ds.value FROM sys.extended_properties ds" +
                $" LEFT JOIN sysobjects tbs ON ds.major_id = tbs.id WHERE ds.minor_id = 0 and tbs.name = TABLE_NAME) as comment" +
                $" FROM INFORMATION_SCHEMA.TABLES";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<TableDAO>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取特定数据库里面的存储过程
        /// </summary>
        public List<ProcDAO> GetAllProcByDb(string connectionString)
        {
            string sql = @"select [name], (select top 1 [text] from syscomments where id=OBJECT_ID(name))
                         from dbo.sysobjects o where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by [name]";
            try
            {
                // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<ProcDAO>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 获取特定数据库里面的视图
        /// </summary>
        public List<ViewDAO> GetAllViewByDb(string connectionString)
        {
            string sql = @"select [name],(select [text] from syscomments where id=OBJECT_ID(name))
                         from dbo.sysobjects o where OBJECTPROPERTY(id, N'IsView') = 1 order by [name]";
            try
            {
                // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<ViewDAO>(sql).ToList();
                }
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据库表列集合
        /// </summary>
        public List<TableColDAO> GetTableCols(string connectionString, string tableName = null)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT [Index] = a.colorder,Title = a.name,IsIdentity = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '0' END,");
            sb.Append("IsPrimaryKeys = CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN(SELECT name  FROM sysindexes WHERE indid IN(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid)) ) THEN '1' ELSE '0' END,");
            sb.Append("DataTypes = b.name,[Length] = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),NullableTypes =  CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END, DefaultValue = ISNULL(e.text, ''), [Desc] = ISNULL(g.[value], '') ");
            sb.Append("FROM syscolumns a LEFT JOIN systypes b  ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id ");
            sb.Append("LEFT JOIN sys.extended_properties g ON a.id = G.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0");

            if (!string.IsNullOrWhiteSpace(tableName))
                sb.Append(" WHERE d.name = '" + tableName + "' ");

            sb.Append(" ORDER BY a.id, a.colorder, d.name");

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    return connection.Query<TableColDAO>(sb.ToString()).ToList();
                }
            }
            catch
            {
                return null;
            }
        }
    }
}
